using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;

using System.Diagnostics; 


namespace crudReportor
{
    public partial class Form1 : Form
    {
        String sqlserver_query = @"CREATE FUNCTION dbo.Fn_findreports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE (
  EmployeeID INT PRIMARY KEY NOT NULL,
  Name       NVARCHAR(255) NOT NULL )
AS
  BEGIN
      WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
           AS (SELECT c.FirstName,
                      e.EmployeeID
               FROM   HumanResources.Employee AS e
                      JOIN Person.Contact AS c
                        ON e.ContactID = c.ContactID
               WHERE  e.EmployeeID = @InEmpID
               UNION ALL
               SELECT c.FirstName,
                      e.EmployeeID,
               FROM   HumanResources.Employee AS e
                      JOIN Person.Contact AS c
                        ON e.ContactID = c.ContactID
                      JOIN DirectReports AS d
                        ON e.ManagerID = d.EmployeeID)
      -- copy the required columns to the result of the function
      INSERT retFindReports
      SELECT EmployeeID,
             Name
      FROM   DirectReports

      RETURN
  END; ";

        String oracle_query = @"CREATE OR REPLACE FUNCTION Dw_dim_contact
 (refresh_period_start_date IN TIMESTAMP,
  etlrunidentifier          IN NUMBER)
RETURN NUMBER
AS
  CURSOR contact_cursor IS
    SELECT cd.contact_identifier,
           Nvl(cd.creation_date, current_timestamp),
           current_timestamp,
           etlrunidentifier
    FROM   cmp_contact c
           LEFT OUTER JOIN contact_dim cd
             ON c.contactid = cd.source_contactid
           LEFT OUTER JOIN local_lookuplistitem li
             ON li.lookuplistitemid = c.typeluid
           LEFT OUTER JOIN local_company cc
             ON cc.companyid = c.companyid
           LEFT OUTER JOIN local_country cty
             ON cty.countrycode = c.country
           LEFT OUTER JOIN contact_dim cd2
             ON c.managercontactid = cd2.source_contactid;
  newcontacts     CONTACT_DIM_TABLE;
  updatecontacts  CONTACT_IDENTIFIER_TABLE;
BEGIN
  OPEN contact_cursor;

  LOOP
      BEGIN
          FORALL i IN INDICES OF newcontacts SAVE EXCEPTIONS
            INSERT INTO contact_dim
            VALUES Newcontacts(i);
      EXCEPTION
          WHEN ex_dml_errors THEN
            l_error_count := SQL%BULK_EXCEPTIONS.COUNT;
      END;

      FORALL i IN updatecontacts.FIRST .. updatecontacts.LAST SAVE EXCEPTIONS
        UPDATE contact_dim
        SET    ROW = Contactarray(i)
        WHERE  contact_identifier = Updatecontacts(i);

      exit WHEN contact_cursor%NOTFOUND;
  END LOOP;

  UPDATE contact_dim c
  SET    c.manager_identifier = (SELECT c2.contact_identifier
                                 FROM   contact_dim c2
                                 WHERE  c2.source_contactid = c.source_managercontactid)
  WHERE  c.modify_etl_run_identifier = etlrunidentifier
         AND c.source_managercontactid IS NOT NULL
         AND c.manager_identifier IS NULL
          OR c.manager_identifier = pkg_dw_seedvalues.dw_getunknowncontactdimmgrid;

  RETURN 0; 
 
end;";

        String db2_query = @"CREATE FUNCTION deptemployees (deptno CHAR(3))
  RETURNS TABLE (empno CHAR(6), lastname VARCHAR(15), firstname VARCHAR(12))
  LANGUAGE SQL
  MODIFIES SQL DATA
  NO EXTERNAL ACTION
  DETERMINISTIC
  BEGIN ATOMIC
    INSERT INTO audit
    VALUES      (USER, 'Table:EMPLOYEE Prd:DEPTNO ='
                        || deptno);

    RETURN SELECT empno,
                  lastname,
                  firstnme
           FROM   employee
           WHERE  employee.workdept = deptemployees.deptno;
  END@  ";
        String mysql_query = @"CREATE PROCEDURE count_born_in_year (year_of_birth INT,
                                     OUT how_many  INT)
BEGIN
  DECLARE c CURSOR FOR
    SELECT Count(*)
    FROM   president
    WHERE  Year(birth) = year_of_birth;

  OPEN c;

  FETCH c INTO how_many;

  CLOSE c;
end$  ";

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cmbDatabases.SelectedIndex = 0;
            txtInputSQL.Text = sqlserver_query;
        }

        private void cmbDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbDatabases.SelectedIndex == 0)
            {
                txtInputSQL.Text = sqlserver_query;
            }
            else if (cmbDatabases.SelectedIndex == 1)
            {
                txtInputSQL.Text = oracle_query;
            }
            else if (cmbDatabases.SelectedIndex == 2)
            {
                txtInputSQL.Text = db2_query;
            }
            else if (cmbDatabases.SelectedIndex == 3)
            {
                txtInputSQL.Text = mysql_query;
            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            TDbVendor dbvendor = TDbVendor.DbVMssql;

            if (cmbDatabases.SelectedIndex == 0)
            {
                dbvendor = TDbVendor.DbVMssql;
            }
            else if (cmbDatabases.SelectedIndex == 1)
            {
                dbvendor = TDbVendor.DbVOracle;
            }
            else if (cmbDatabases.SelectedIndex == 2)
            {
                dbvendor = TDbVendor.DbVDB2;
            }
            else if (cmbDatabases.SelectedIndex == 3)
            {
                dbvendor = TDbVendor.DbVMysql;
            }
            crudReportor r = new crudReportor(txtInputSQL.Text,dbvendor);
            txtOutput.Text = r.report();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Process ps = new Process();
            string yourURL = "http://www.sqlparser.com/features/crud-reportor.php?ref=csharp_demo";
            ps.StartInfo.FileName = "iexplore.exe";
            ps.StartInfo.Arguments = yourURL;
            ps.Start();  

        }

        private void button3_Click(object sender, EventArgs e)
        {
            Process ps = new Process();
            string yourURL = "http://www.dpriver.com/blog/anti-sql-injection-tool/?ref=csharp_demo";
            ps.StartInfo.FileName = "mailto:info@sqlparser.com";
            ps.StartInfo.Arguments = yourURL;
            ps.Start();  

        }
    }
}